package com.situ.web.controller;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import com.situ.web.entity.Banji;
import com.situ.web.entity.Student;
import com.situ.web.entity.User;
import com.situ.web.service.IBanjiService;
import com.situ.web.service.impl.BanjiServiceImpl;
import com.situ.web.util.JDBCUtil;
import com.situ.web.vo.StudentBanjiVO;

@WebServlet("/student")
public class StudentServlet extends HttpServlet{
	// private IStudentService studentService = new StudentServiceImpl();
	private IBanjiService banjiService = new BanjiServiceImpl();

	@Override
	protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		// 解决post请求乱码问题，这句话要放在最前面
		//req.setCharacterEncoding("UTF-8");
		
		// 判断用户有没有登录，如果已经登录就继续往下访问，没有登录跳转到登录界面
//		HttpSession session = req.getSession();
//		User user = (User) session.getAttribute("user");
//		if (user == null) {
//			resp.sendRedirect(req.getContextPath() + "/login.jsp");
//			return;
//		}
		
		
		// http://localhost:8080/Java202007Web/studentSelect
		// http://localhost:8080/Java202007Web/studentDelete?id=2
		
		// http://localhost:8080/Java202007Web/student?method=selectAll
		// http://localhost:8080/Java202007Web/student?method=delete&id=2
		String method = req.getParameter("method");
		// 如果这个method=null或者是""字符串，就让这么method的值为selectAll
		if (method == null || "".equals(method)) {
			method = "selectAll";
		}
		switch (method) {
		case "selectAll":
			selectAll(req, resp);
			break;
		case "selectByName":
			selectByName(req, resp);
			break;
		case "delete":
			delete(req, resp);
			break;
		case "insert":
			insert(req, resp);
			break;
		case "selectById":
			selectById(req, resp);
			break;
		case "update":
			update(req, resp);
			break;
		case "getStudentInsertPage":
			getStudentInsertPage(req, resp);
			break;
		default:
			break;
		}
	}

	private void getStudentInsertPage(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		List<Banji> list = banjiService.selectAll();
		
		req.setAttribute("list", list);
		req.getRequestDispatcher("/student_insert.jsp").forward(req, resp);
	}

	private void selectByName(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		String searchName = req.getParameter("searchName");
		
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		ArrayList<Student> list = new ArrayList<Student>();
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT `id`,`name`,`age`,`gender` FROM `student` where name like ?";
			statement = connection.prepareStatement(sql);
			statement.setString(1, "%" + searchName + "%");
			System.out.println(statement);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				String gender = resultSet.getString("gender");
				Student student = new Student(id, name, age, gender);
				list.add(student);
			}
			for (Student student : list) {
				System.out.println(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, statement, resultSet);
		}
		
		// 把数据放到request里面，就相当于一块内存区域
		req.setAttribute("list", list);
		// 转发（跳转）到student_list.jsp页面进行展示
		req.getRequestDispatcher("/student_list.jsp").forward(req, resp);
	}

	private void update(HttpServletRequest req, HttpServletResponse resp) throws IOException {
		String id = req.getParameter("id");
		String name = req.getParameter("name");
		String age = req.getParameter("age");
		String gender = req.getParameter("gender");
		
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "update student set name=?,age=?,gender=? where id=?";
			statement = connection.prepareStatement(sql);
			// 填上?占位符
			statement.setString(1, name);
			statement.setInt(2, Integer.parseInt(age));
			statement.setString(3, gender);
			statement.setInt(4, Integer.parseInt(id));;
			System.out.println(statement);
			int count = statement.executeUpdate();
			System.out.println("count: " + count);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, statement, null);
		}
		
		resp.sendRedirect(req.getContextPath() + "/student?method=selectAll");
	}

	private void selectById(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		String selectId = req.getParameter("id");
		
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		Student student = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT `id`,`name`,`age`,`gender` FROM `student` where id=?";
			statement = connection.prepareStatement(sql);
			statement.setInt(1, Integer.parseInt(selectId));
			System.out.println(statement);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				String gender = resultSet.getString("gender");
				student = new Student(id, name, age, gender);
			}
			System.out.println(student);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, statement, resultSet);
		}
		
		req.setAttribute("student", student);
		req.getRequestDispatcher("/student_update.jsp").forward(req, resp);
	}

	private void insert(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException {
		String name = req.getParameter("name");
		String age = req.getParameter("age");
		String gender = req.getParameter("gender");
		String banjiId = req.getParameter("banjiId");
		
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "INSERT INTO student(`name`,age,gender,banji_id) VALUES(?,?,?,?)";
			// return generated keys
			statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			// 填上?占位符
			statement.setString(1, name);
			statement.setInt(2, Integer.parseInt(age));
			statement.setString(3, gender);
			statement.setInt(4, Integer.parseInt(banjiId));
			System.out.println(statement);
			int count = statement.executeUpdate();
			System.out.println("count: " + count);
			
			resultSet = statement.getGeneratedKeys();
			int id=0;//保存生成的ID
			if (resultSet != null && resultSet.next()) {
			    id = resultSet.getInt(1);
			}
			System.out.println("id: " + id);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, statement, null);
		}
		
		// 删除完了之后重定向到查找所有界面 /studentSelect
		// req.getContextPath()得到项目的名字
//		resp.sendRedirect(req.getContextPath() + "/studentSelect");
		resp.sendRedirect(req.getContextPath() + "/student?method=selectAll");
	}

	private void selectAll(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		ArrayList<StudentBanjiVO> list = new ArrayList<StudentBanjiVO>();
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT s.id studentId,s.name studentName,s.age studentAge,s.gender studentGender,b.id banjiId,b.name AS banjiName FROM student AS s INNER JOIN banji AS b ON s.banji_id=b.id;";
			statement = connection.prepareStatement(sql);
			resultSet = statement.executeQuery();
			System.out.println(statement);
			while (resultSet.next()) {
				int studentId = resultSet.getInt("studentId");
				String studentName = resultSet.getString("studentName");
				int studentAge = resultSet.getInt("studentAge");
				String studentGender = resultSet.getString("studentGender");
				int banjiId = resultSet.getInt("banjiId");
				String banjiName = resultSet.getString("banjiName");
				//Banji banji = new Banji();
				//banji.setName(banjiName);
				
				StudentBanjiVO studentBanjiVO = new StudentBanjiVO(studentId, studentName, studentAge, studentGender, banjiId, banjiName);
				list.add(studentBanjiVO);
			}
			for (StudentBanjiVO studentBanjiVO : list) {
				System.out.println(studentBanjiVO);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, statement, resultSet);
		}
		
		// 把数据放到request里面，就相当于一块内存区域
		req.setAttribute("list", list);
		// 转发（跳转）到student_list.jsp页面进行展示
		req.getRequestDispatcher("student_list.jsp").forward(req, resp);
	}
	
	private void delete(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException {
		 // http://localhost:8080/Java202007Web/studentDelete?id=2
		// 从浏览器传递过来的数据即使是数值类型得到的也是字符串类型
		String id = req.getParameter("id");
		
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "delete from student where id=?";
			statement = connection.prepareStatement(sql);
			statement.setInt(1, Integer.parseInt(id));
			System.out.println(statement);
			int count = statement.executeUpdate();
			System.out.println("count: " + count);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, statement, null);
		}
		
		// 删除完了之后重定向到查找所有界面 /studentSelect
		// req.getContextPath()得到项目的名字
//		resp.sendRedirect(req.getContextPath() + "/studentSelect");
		//resp.sendRedirect(req.getContextPath() + "/student?method=selectAll");
		
		
	}

}
